--[[
	Copyright (c) 2018 James Dornan <james@catch22.com>
	
	Permission is hereby granted, free of charge, to any person obtaining a copy
	of this software and associated documentation files (the "Software"), to deal
	in the Software without restriction, including without limitation the rights
	to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
	copies of the Software, and to permit persons to whom the Software is
	furnished to do so, subject to the following conditions:
	
	The above copyright notice and this permission notice shall be included in all
	copies or substantial portions of the Software.
	
	THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
	IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
	FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
	AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
	LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
	OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
	SOFTWARE.
--]]

--- dbtable.sqlite3.lua
-- Allows access to a SQLite3 table as a Lua table. All data is compressed and
-- encoded when used in the context of a minetest mod.
--
-- @module dbtable.sqlite3
-- @author James Dornan <james@catch22.com>
-- @copyright James Dornan <james@catch22.com> 2018
-- @release 1.0

---@usage
local usage = [[
local tbl = require("dbtable").new({
    dn   = "sqlite3"
    name = "test",
    file = "/tmp/test.sqlite3"
})

tbl[1] = 'b'
tbl[2] = 'c'
tbl[3] = 'e'

The above example would create a sqlite3 database, and then create within it
a table called "test" with 2 columns, key and value. Key it set as a primary
key in the database, and must be unique.

This would be the result.

$sqlite3 /tmp/test.sqlite3
sqlite> .schema
CREATE TABLE test
            (
                key   VARCHAR(54) PRIMARY KEY,
                value VARCHAR(512)
            );
sqlite> SELECT * FROM test;
1|b
2|c
3|e
sqlite>
]]

local sqlite3 = {}

--- Constructor function for sqlite3table module
--
--  See: <a href=https://keplerproject.github.io/luasql/>luasql</a>,
--  <a href=https://forum.minetest.net/viewtopic.php?t=12471/>Trusted mods</a>,
--  <a href=http://lua-users.org/wiki/MetatableEvents/>Metatable Events</a>
--
-- @param opts table of options
-- @return Table object that has all the metamethods for acting as a Lua table [table]
function sqlite3.new(opts)
    assert(type(opts.name) == "string")
    assert(type(opts.file) == "string")

    --- table: Used as self in the object
    local self        = {}
    --- table: Metatable to enable metamethods to impersonate a lua table
    local metatable   = {}
    --- string: Name of the sqlite3 table we will use
    local name        = opts.name
    --- string: Name of the sqlite3 database file
    local fileName    = opts.file
    --- int: Maximum key size
    local keySize     = 54
    --- int: Maximum value size
    local valueSize   = 512
    --- int: Minimum bytes before compression
    local minCompress = 50
    --- table: List of legal key types
    local legalKeys   = {
        string = true,
        number = true
    }
    --- table: List of legal value types
    local legalValues = {
        string  = true,
        number  = true,
        boolean = true
    }
    --- class: module luasql.sqlite3
    local driver      = assert(
        require "luasql.sqlite3",
        "Please install luasql. https://keplerproject.github.io/luasql/"
    )
    --- class: sqlite3 driver
    local env         = assert(driver.sqlite3())
    --- resource: connection to sqlite3 database
    local con         = assert(env:connect(fileName))
    --- resource: cursor used for return data
    local cur

    --- Set autocommit, or fail.
    assert(con:setautocommit(true))

    --- function: serialize
    local serialize
    --- function: deserialize
    local deserialize

    --- function: compress
    local compress
    --- function: decompress
    local decompress

    if opts.serialize and opts.deserialize then
        serialize        = opts.serialize
        deserialize      = opts.deserialize
    end

    -- Add table as a legal value type
    if serialize then
        legalValues.table = true
    end

    if opts.compress and opts.decompress then
        compress        = opts.compress
        decompress      = opts.decompress
    end

    --- boolean: True if the sqlite3 table was created using this instance
    self.created      = false

    --- Returns true is the table exists and false if it does not.
    -- @return boolean
    function self:exists()
        local cur = assert(con:execute(string.format(
            [[SELECT 1 FROM sqlite_master WHERE tbl_name = '%s']],
            con:escape(name)
        )))

        if cur == nil then
            return false
        end

        local res = cur:fetch()
        cur:close()
        return res ~= nil
    end

    --- Locates the next lowest numberic key to use
    -- @return The next available numeric key [number]
    function self:nextKey()
       local cur = assert(con:execute(string.format(
           [[
               SELECT  key + 1 AS nextkey
               FROM    %s outside
               WHERE   abs(outside.key) > 0 AND NOT EXISTS
               (
                   SELECT NULL
                   FROM   %s inside
                   WHERE  abs(inside.key) > 0 AND inside.key = outside.key + 1
               )
               ORDER BY key LIMIT 1
           ]],
           con:escape(name),
           con:escape(name)
        )))

        if cur == nil then
            return
        end

       local row = cur:fetch({}, "a")
       cur:close()

       if not row then
           return 0
       end

       return row.nextkey
    end

    --- Drop the table from the database
    -- @return void
    function self:drop()
        local cur = assert(con:execute(string.format([[
                DROP TABLE %s
            ]],
            con:escape(name)
        )))
    end

    --- The number of rows in the sqlite3 table we're using
    -- @return number of rows [number]
    function self:len()
       local cur = assert(con:execute(string.format(
           [[SELECT count(1) AS count FROM %s]],
           con:escape(name)
        )))

        if cur == nil then
            return
        end

       local row = cur:fetch({}, "a")
       cur:close()

       return row.count
    end

    --- Get a value from the table using a key
    -- @param key, either string or number
    -- @return string value from the table
    function self:get(key)
        assert(legalKeys[type(key)], "Only number or string key.")

        local cur = assert(con:execute(string.format(
            [[SELECT value FROM %s WHERE key = '%s']],
            con:escape(name),
            con:escape(key)
        )))

        if cur == nil then
            return
        end

        local row = cur:fetch({}, "a")

        cur:close()

        if row == nil then
            return
        end

        local value = row.value

        if decompress and string.sub(value, 1, 8) ~= "return {" then
             value = decompress(value)
        end

        if deserialize and string.sub(value, 1, 8) == "return {" then
             value = deserialize(value)
        end

        return value
    end

    --- Get the next row after this key in order. Useful for iteration.
    -- @param[opt] key, string or number, before the desired row
    -- @return[1] key
    -- @return[2] value
    function self:next(key)
        assert(legalKeys[type(key)] or key == nil, "Only number, string, or nil.")

        local cur

        if key then
            cur = assert(con:execute(string.format(
                [[SELECT key, value FROM %s WHERE key + 1 > '%s' + 1 ORDER BY key + 1 LIMIT 1]],
                con:escape(name),
                con:escape(key)
            )))
        else
            cur = assert(con:execute(string.format(
                [[SELECT key, value FROM %s ORDER BY key + 1 LIMIT 1]],
                con:escape(name)
            )))
        end

        if cur == nil then
            return
        end

        local row = cur:fetch({}, "a")

        cur:close()

        if row == nil then
            return
        end

        local value = row.value

        if decompress and string.sub(value, 1, 8) ~= "return {" then
             value = decompress(value)
        end

        if deserialize and string.sub(value, 1, 8) == "return {" then
             value = deserialize(value)
        end

        return row.key, value
    end

    --- Get the smallest key value in the table
    -- @return the lowest key in the table
    function self:minKey()
        local cur = assert(con:execute(string.format(
            [[SELECT min(key) AS key FROM %s']],
            con:escape(name)
        )))

        if cur == nil then
            return
        end

        local row = cur:fetch({}, "a")
        cur:close()

        if row ~= nil then
            return row.key
        end
    end

    --- Create table in sqlite3 based on the name, keysize and valuesize
    -- @return boolean based on success
    function self:create()
        if type(opts.keysize) == "number" then
            keySize = opts.keysize
        end

        assert((keySize > 0 and keySize < 512), 'Key size out of range.')

        if type(opts.valuesize) == "number" then
            valueSize = opts.valuesize
        end

	print('valueSize ' .. valueSize)
        assert((valueSize > 0 and valueSize <= 8192), 'Value size out of range.')

        local res = assert(con:execute(string.format([[
            CREATE TABLE IF NOT EXISTS %s
            (
                key   VARCHAR(%u) PRIMARY KEY,
                value VARCHAR(%u)
            )
        ]], con:escape(name), keySize, valueSize)))

        return type(res) == "number"
    end

    --- Set new key value pair in the table
    -- @param key, string or number
    -- @param[opt] value If omitted the row is deleted
    -- @return value returned from the luasql execite fuction
    function self:set(key, value)
        assert(legalKeys[type(key)], "Only number or string.")

        if minetest ~= nil then
            minetest.log("info", "MTM set " .. key)
        end

        if value == nil then
            return self.del(con, name, key)
        end

        assert(legalValues[type(value)], "Only number, string, or table.")

        if serialize and type(value) == "table" then
            value = serialize(value)
        end

        if compress and string.len(value) > minCompress then
             value = compress(value)
        end

        local res = assert(con:execute(string.format(
            [[REPLACE INTO %s VALUES ('%s', '%s')]],
            con:escape(name),
            con:escape(key),
            con:escape(value)
        )))

        return res
    end

    --- Delete row from table
    -- @param key The unique key to be deleted
    -- @return value returned from the luasql execite fuction
    function self:del(key)
        return assert(con:execute(string.format([[
            DELETE FROM  %s WHERE key = '%s'
        ]], name, key)))
    end

    --- Create copy of the database table as a lua table
    -- @return table form of the data
    function self:export()
        local cur = assert(con:execute(string.format([[
            SELECT key, value FROM %s ORDER BY key ASC;
        ]], name)))

        local data = {}
        local row = cur:fetch ({}, "a")

        while row do
            data[row.key] = row.value
            row = cur:fetch ({}, "a")
        end

        return data
    end

    --- Import lua table into database table
    -- @return void
    function self:import(data)
        for key,value in pairs(data) do
            self:set(key, value)
        end
    end

    --- Lookup row from sqlite3 table
    -- @param key Key to look up in the table
    -- @return mixed, string , int, or table
    function metatable:__index(key)
        return self:get(key)
    end

    --- Add or up a row in the sqlite3 table
    -- @param key The key to look up in the table
    -- @param value The new value to be added to the table
    -- @return boolean
    function metatable:__newindex(key, value)
        return self:set(key, value)
    end

    --- Append numerically
    -- @usage tbl(value) same as tbl[#tbl +] = value
    -- @param value Value to be added to the next numerical key in order
    function metatable:__call(value)
        return self:set(self:nextKey(), value)
    end

    --- Next key/value test from the given key
    -- Fails for lua 5.2 & luajit 2.1.0
    -- @param key Key before the row we want to return
    -- @return key, value
    function metatable:__next(key)
        return self:next(key)
    end

    --- Used by the pairs function to loop through the table
    -- Fails for luajit 2.1.0
    -- @return function, table, nil
    function metatable:__pairs()
        local cur = assert(con:execute(string.format([[
            SELECT key, value FROM %s ORDER BY key ASC;
        ]], name)))

        local pairsNext = function(table, key)
            if cur == nil then
                return
            end

            row = cur:fetch({}, "a")

            if row ~= nil then
                return row.key, row.value
            end

            cur:close()
        end
        return pairsNext, self, nil
    end

    --- Used by the ipairs function to loop through the table
    -- Fails for luajit 2.1.0
    -- @return function, table, 0
    function metatable:__ipairs()
        local pairsNext = function(table, key)
            local value
            key = key + 1

            value = self:get(key)

            if value then
                return key, value
            end
        end
        return pairsNext, self, 0
    end

    --- For the # table length method.
    -- Fails for luajit 2.1.0, use len()
    -- @return int Number of rows in the table
    function metatable:__len()
        return self:len()
    end

    --- String context
    -- @return string
    -- @todo Not sure what to do here at the moment
    function metatable:__tostring()
      return "File: " .. file .. " Table: " .. name
    end

    --- Mode
    -- @return string
    function metatable:__mode()
      return 'kv'
    end

    --- Garbage collection, gets called with there are no more references to the
    -- object.
    function metatable:__gc()
        pcall(cur:close())
        pcall(con:close())
        pcall(env:close())
    end

    metatable.__metatable = {}

    setmetatable(self, metatable)

    if self:exists() then
      return self
    end

    assert(self:create(), 'Unable to create database table.')
    self.created = true

    return self
end

return sqlite3
